﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Text;
using Kugar.Core.Data.DBCommon;
using System.Data.SQLite;
using Kugar.Core.ExtMethod;

#if SQLite
namespace Kugar.Core.Data.SqliteHelper
{
    public class SqliteHelper : DbDatabaseHelperBase
    {
        public SqliteHelper(string connString) : base(connString)
        {

        }

        public SqliteHelper(DbConnection conn) : base(conn)
        {

        }

        protected override void OpenConnection()
        {
            if (Connection == null || Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken)
            {
                if (ConnectionString.IsNullOrEmpty())
                {
                    throw new NoNullAllowedException("连接字符串不能为空");
                }

                try
                {
                    if (Connection == null)
                    {
                        Connection = new SQLiteConnection();
                    }

                    Connection.ConnectionString = ConnectionString;

                    Connection.Open();

                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        protected override void ExecuteDataTableInternal(DbCommand cmd, DataTable tbl)
        {
            var da = new SQLiteDataAdapter((SQLiteCommand)cmd);

            try
            {
                da.Fill(tbl);
            }
            finally
            {
                cmd.Transaction = null;
                cmd.Connection = null;
                cmd.Dispose();

                da.Dispose();
            }

        }

        protected override void ExecuteDataSetInternal(DbCommand cmd, DataSet ds)
        {
            var da = new SQLiteDataAdapter((SQLiteCommand)cmd);

            try
            {
                da.Fill(ds);
            }
            finally
            {
                cmd.Transaction = null;
                cmd.Connection = null;
                cmd.Dispose();

                da.Dispose();
            }

        }


        private const string getSchemaSqlstr = @"select * from {0} limit 0,1";
        public override DataTable GetDatabaseTableSchema(string srcTableName, SchemaType schemaType)
        {
            if (srcTableName.IsNullOrEmpty())
            {
                throw new NoNullAllowedException("源表名不能为空");
            }

            var sqlstr = string.Format(getSchemaSqlstr, srcTableName);

            var da = new SQLiteDataAdapter(sqlstr,(SQLiteConnection)Connection);

            var tbl = new DataTable(srcTableName);

            da.FillSchema(tbl, schemaType);

            return tbl;
        }

        public override DataSet GetDatabaseAllTableSchema(SchemaType schemaType)
        {
            var nameList = GetAllTableNames();

            if (nameList.IsEmptyData())
            {
                return null;
            }

            var cmd =(SQLiteCommand) Connection.CreateCommand();
            var da = new SQLiteDataAdapter(cmd);
            var ds = new DataSet("tableSchema");

            foreach (string tableName in nameList)
            {
                var sqlstr = string.Format(getSchemaSqlstr, tableName);

                cmd.CommandText = sqlstr;

                var tbl = new DataTable(tableName);

                da.FillSchema(tbl, schemaType);

                ds.Tables.Add(tbl);
            }

            return ds;
        }


        private const string getAllTableNameSql = @"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
        public override string[] GetAllTableNames()
        {
            var nameList = new List<string>();

            using(var dr=ExecuteReader(getAllTableNameSql))
            {
                if (!dr.HasRows)
                {
                    return null;
                }

                while (dr.Read())
                {
                    nameList.Add(dr.GetString(1));
                }
            }

            return nameList.ToArray();
        }


        protected override string BuildPagingSql(PagingArgs args)
        {
            var startRecIndex = ((args.PageIndex - 1)*args.PageSize);
            var getRecCount = args.PageSize;
            var orderSql = CreateOrderSql(args.OrderSql, args.KeyField);

            const string sqlstr = @"Select {4} 
                                                 from 
                                                 (
                                                        {0}
                                                ) order by {1} limit {2},{3}
                                                ";

            args.EnsurePageIndex();

            return string.Format(sqlstr, args.TableSql, orderSql,
                                 startRecIndex, getRecCount, args.ColumnSql);
        }

        protected override string BuildGetRecordCountSql(PagingArgs args)
        {
            const string sqlstr = @"Select count(*) 
                                                 from 
                                                 (
                                                        {0}
                                                ) f
                                                ";

            return string.Format(sqlstr, args.TableSql);
        }

        private static string CreateOrderSql(string orderSql, string keyField)
        {
            if (string.IsNullOrEmpty(keyField)) throw new ArgumentException("必须设置keyField", keyField);

            string[] keyFields = keyField.Split(',');

            const string noContansOrderSql = "{0} {1} ";
            const string contansOrderSql = ", {0} {1} ";

            var str = string.IsNullOrEmpty(orderSql) ? noContansOrderSql : contansOrderSql;

            if (string.IsNullOrEmpty(orderSql))
            {
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < keyFields.Length; ++i)
                {
                    if (i != 0) sb.Append(",");
                    sb.AppendFormat("{0} asc", keyFields[i]);
                }
                return sb.ToString();
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(orderSql);
                foreach (var field in keyFields)
                {
                    if (!orderSql.Contains(field))
                    {
                        sb.AppendFormat(",{0} asc", field);
                    }
                }
                return sb.ToString();
            }
        }
    }
}
#endif


